import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.family'] = 'Microsoft YaHei'

# 1. 加载三张表数据
file_path_balance = '../雪龙集团资产负债表.xlsx'
file_path_profit = '../雪龙集团利润表.xlsx'
file_path_cashflow = '../雪龙集团现金流量表.xlsx'

# 读取资产负债表
df_balance = pd.read_excel(file_path_balance, header=None)
df_balance.columns = ["项目"] + [2020, 2021, 2022, 2023]

# 读取利润表
df_profit = pd.read_excel(file_path_profit, header=None)
df_profit.columns = ["项目"] + [2020, 2021, 2022, 2023]

# 读取现金流量表
df_cashflow = pd.read_excel(file_path_cashflow, header=None)
df_cashflow.columns = ["项目"] + [2020, 2021, 2022, 2023]

# 转换数值列（去除逗号并转换为数字）
for col in [2020, 2021, 2022, 2023]:
    df_balance[col] = pd.to_numeric(df_balance[col].replace(',', '', regex=True), errors='coerce')
    df_profit[col] = pd.to_numeric(df_profit[col].replace(',', '', regex=True), errors='coerce')
    df_cashflow[col] = pd.to_numeric(df_cashflow[col].replace(',', '', regex=True), errors='coerce')

# 2. 提取所需数据
# 从资产负债表提取总资产和总负债
total_assets = df_balance[df_balance["项目"] == "资产总计(元)"].iloc[0, 1:]
total_liabilities = df_balance[df_balance["项目"] == "负债合计(元)"].iloc[0, 1:]

# 计算所有者权益
equity = total_assets - total_liabilities

# 补充所有者权益细项（如未分配利润）
if "未分配利润(元)" in df_profit["项目"].values:
    undistributed_profit = df_profit[df_profit["项目"] == "未分配利润(元)"].iloc[0, 1:]
    equity += undistributed_profit

# 3. 计算偿债能力指标
# 资产负债率 = 总负债 / 总资产 * 100
debt_to_asset_ratio = (total_liabilities / total_assets) * 100

# 产权比率 = 总负债 / 所有者权益
debt_to_equity_ratio = total_liabilities / equity

# 4. 绘制折线图
plt.figure(figsize=(10, 6))

# 绘制资产负债率
plt.plot([2020, 2021, 2022, 2023], debt_to_asset_ratio, marker='o', label="资产负债率（%）", color='blue')

# 绘制产权比率
plt.plot([2020, 2021, 2022, 2023], debt_to_equity_ratio, marker='o', label="产权比率", color='green')

# 添加标题和标签
plt.title("雪龙集团2020-2023年偿债能力分析", fontsize=16)
plt.xlabel("年份", fontsize=12)
plt.ylabel("比率", fontsize=12)

# 添加图例和网格
plt.legend(loc="best", fontsize=10)
plt.grid(alpha=0.5)
plt.tight_layout()

plt.savefig('../分析图象/偿债能力分析图表.png')
# 显示图表
plt.show()
